using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;

public class DatabaseHelper
{

    //使用方法：
    //DatabaseHelper dbHelper = new DatabaseHelper(Form1.add);
    //DataTable dt1 = dbHelper.ReturnDataTable1("a3_inspection_list");

    private string connectionString;

    public DatabaseHelper(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public int Insert(string table, string columns, string values)
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlstr = $"INSERT INTO {table} ({columns}) VALUES ({values});";
                using (var cmd = new MySqlCommand(sqlstr, con))
                {
                    cmd.ExecuteNonQuery();
                    return (int)cmd.LastInsertedId;
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
            return 0; // 或者可以抛出异常
        }
    }

    public string Query(string table, string content, string condition = "")
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = string.IsNullOrEmpty(condition) ? $"SELECT {content} FROM {table}" : $"SELECT {content} FROM {table} WHERE {condition}";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return reader[content].ToString();
                        }
                    }
                }
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine(ex.Message);
        }
        return null;
    }

    public bool Update(string table, string updateCommand, string condition)
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = $"UPDATE {table} SET {updateCommand} WHERE {condition};";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString(), "错误，请截图并联系管理员！", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return false;
        }
    }

    public bool Delete(string table, string condition)
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = $"DELETE FROM {table} WHERE {condition};";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }

    public DataTable ReturnDataTable(string table, string condition = "")
    {
        DataTable dt = new DataTable();
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = string.IsNullOrEmpty(condition) ? $"SELECT * FROM {table}" : $"SELECT * FROM {table} WHERE {condition}";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    using (var adapter = new MySqlDataAdapter(cmd))
                    {
                        adapter.Fill(dt);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return dt;
    }

    public DataTable ReturnDataTable1(string table, string columnName, string condition = "")
    {
        DataTable dt = new DataTable();
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = string.IsNullOrEmpty(condition) ? $"SELECT * FROM {table} order by CONVERT(" + columnName + " USING gbk ) COLLATE gbk_chinese_ci ASC" : $"SELECT * FROM {table} WHERE {condition} order by CONVERT(" + columnName + " USING gbk ) COLLATE gbk_chinese_ci ASC";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    using (var adapter = new MySqlDataAdapter(cmd))
                    {
                        adapter.Fill(dt);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return dt;
    }

    public List<string> ReturnColumnValues(string table, string columnName, string condition = "")
    {
        List<string> values = new List<string>();
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = string.IsNullOrEmpty(condition) ? $"SELECT {columnName} FROM {table}" : $"SELECT {columnName} FROM {table} WHERE {condition}";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            values.Add(reader[columnName].ToString());
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return values;
    }

    public int ReturnRowsCount(string table, string condition = "")
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = string.IsNullOrEmpty(condition) ? $"SELECT COUNT(*) FROM {table}" : $"SELECT COUNT(*) FROM {table} WHERE {condition}";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    return Convert.ToInt32(cmd.ExecuteScalar());
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return 0;
        }
    }

    public List<string> ReturnTableNames()
    {
        List<string> tableNames = new List<string>();
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = "SHOW TABLES;";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            tableNames.Add(reader[0].ToString());
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return tableNames;
    }

    public string[] ReturnArray(string table, string condition, string columnName)
    {
        List<string> results = new List<string>();
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = $"SELECT {columnName} FROM {table} WHERE {condition};";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            results.Add(reader[columnName].ToString());
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return results.ToArray();
    }

    public bool FindRow(string table, string columnName, string rowName)
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = $"SELECT * FROM {table} WHERE {columnName} = '{rowName}';";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        return reader.HasRows;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }

    public int TotalColumnsQuantity(string table)
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = $"SELECT * FROM `{table}`;";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    using (var adapter = new MySqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        adapter.Fill(dt);
                        return dt.Columns.Count;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return 0;
        }
    }

    public bool CopyTable(string newDatabase, string newTable, string oldDatabase, string oldTable)
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                // 创建新表
                string createTableCmd = $"CREATE TABLE IF NOT EXISTS `{newDatabase}`.`{newTable}` LIKE `{oldDatabase}`.`{oldTable}`;";
                using (var cmd = new MySqlCommand(createTableCmd, con))
                {
                    cmd.ExecuteNonQuery();
                }

                // 插入数据
                string insertDataCmd = $"INSERT INTO `{newDatabase}`.`{newTable}` SELECT * FROM `{oldDatabase}`.`{oldTable}`;";
                using (var cmd = new MySqlCommand(insertDataCmd, con))
                {
                    cmd.ExecuteNonQuery();
                }
                return true;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }

    public bool DeleteTable(string table)
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = $"DROP TABLE IF EXISTS `{table}`;";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }

    public bool TruncateTable(string table)
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlCmd = $"TRUNCATE TABLE `{table}`;";
                using (var cmd = new MySqlCommand(sqlCmd, con))
                {
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }

    public bool CreateTable(string newTable)
    {
        try
        {
            using (var con = new MySqlConnection(connectionString))
            {
                con.Open();
                string sqlstr = $"CREATE TABLE `{newTable}` (`ID` INT NOT NULL AUTO_INCREMENT, `Item` VARCHAR(100) NULL, `Min` VARCHAR(45) NULL, `Max` VARCHAR(45) NULL, PRIMARY KEY (`ID`));";
                using (var cmd = new MySqlCommand(sqlstr, con))
                {
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }
}